﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace DAL
{
    public class GenericSearchDAL : DB
    {
        public GenericSearchDAL(LoginInfo info)
        {
            base.loginInfo = info;
        }
        //public readonly static GenericSearchDAL Instance = new GenericSearchDAL();

        #region "通用查询数据源"
        public DataTable GetViewData(string tableName, string order, string sort ,string[] sqlExt, IDataParameter[] para)
        {
            try
            {
                HIDB.dbo con = base.conn();
                DataTable dtResult = new DataTable();

                string orderBy = string.Format(" ORDER BY {0} {1}", sort, order);
                string sqlWhere = string.Empty;
                if (sqlExt.Length > 0)
                {
                    sqlWhere = string.Format(" where {0}", string.Join(" and ", sqlExt));
                }
                string sql = string.Format("SELECT * FROM {0} {1} {2} offset @Rows * (@PageNum-1) rows fetch next @Rows rows only", tableName, sqlWhere, orderBy);//owner.tablename
                dtResult = con.GetDataTable(sql, para);
                return dtResult;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public int GetViewDataCount(string schemaName, string viewName, DataTable whereCondition)
        {
            int result = 0;
            try
            {
                //创建数据库对象
                HIDB.dbo con = base.conn(); //HIDB.common.make();
                SqlParameter[] sqlparams = new SqlParameter[]
                { 
                     new  SqlParameter("@SchemaName ",schemaName)
                    ,new  SqlParameter("@ViewName",viewName)
                    ,new  SqlParameter("@WhereCondition",whereCondition)
                };
                DataTable dt = con.StoredDataTable("[Working_db].[UP_GenericSearch_GetViewDataCount]", sqlparams);
                if (dt != null && dt.Rows.Count > 0)
                {
                    result = Int32.Parse(dt.Rows[0]["RowsCount"].ToString());
                }
            }
            catch (Exception)
            {
                throw;
            }
            return result;
        }

        public string GetTableData(string schemaName, string tableName, DataTable whereCondition, int pageSize, int pageIndex)
        {
            string result = string.Empty;
            try
            {
                HIDB.dbo con = base.conn(); //HIDB.common.make();
                //添加输入输出参数
                SqlParameter[] sqlparams = new SqlParameter[]
                { 
                    new  SqlParameter("@SchemaName ",schemaName)
                    ,new  SqlParameter("@TableName  ",tableName)
                    ,new  SqlParameter("@WhereCondition",whereCondition)
                    ,new  SqlParameter("@pageSize",pageSize)
                    ,new  SqlParameter("@pageIndex",pageIndex)
                };
                result = con.StoredDataTable("[Working_db].[UP_GenericSearch_GetTableData]", sqlparams).Rows[0][0].ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        public DataTable GetTableData(string modelID,  string sort, string order, string[] sqlExt, IDataParameter[] para)
        {
            //创建数据库对象
            HIDB.dbo con = base.conn();
            DataTable dtResult = new DataTable();

            string orderBy = string.Empty;
            if (!string.IsNullOrEmpty(sort))
            {
                orderBy = string.Format(" ORDER BY [{0}] {1}", sort, order);
            }
            string sqlWhere = string.Empty;
            if (sqlExt.Length > 0)
            {
                sqlWhere = string.Format(" where {0}", string.Join(" and ", sqlExt));
            }
            string sql = string.Format("SELECT top 11 * FROM [Fact].[ODS_{0}_DQ] {1} {2}", modelID, sqlWhere, orderBy);//owner.tablename

            if (para == null)
                dtResult = con.GetDataTable(sql);
            else
                dtResult = con.GetDataTable(sql, para);
            return dtResult;
        }
        
        public int GetTableDataCount(string schemaName, string tableName, DataTable whereCondition)
        {
            int result = 0;
            try
            {
                //创建数据库对象
                HIDB.dbo con = base.conn(); //HIDB.common.make();
                SqlParameter[] sqlparams = new SqlParameter[]
                { 
                     new  SqlParameter("@SchemaName ",schemaName)
                    ,new  SqlParameter("@TableName",tableName)
                    ,new  SqlParameter("@WhereCondition",whereCondition)
                };
                DataTable dt = con.StoredDataTable("[Working_db].[UP_GenericSearch_GetTableDataCount]", sqlparams);
                if (dt != null && dt.Rows.Count > 0)
                {
                    result = Int32.Parse(dt.Rows[0]["RowsCount"].ToString());
                }
            }
            catch (Exception)
            {
                throw;
            }
            return result;
        }
        #endregion

        public bool DeleteDQ(string tableName, string date)
        {
            try
            {
                HIDB.dbo conn = base.conn(); //HIDB.common.make();
                string sql = string.Format("delete from {0} where BatchTime<'{1}'", tableName, date);
                conn.ExecuteSql(sql);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public string GetDQTables(string tableName)
        {
            HIDB.dbo conn = base.conn(); //HIDB.common.make();
            string sql = @"select distinct c.SchemaName,c.TableName,c.TableNameFull from
                                    (
	                                    select SCHEMA_NAME(a.schema_id) as SchemaName,a.name as TableName,SCHEMA_NAME(a.schema_id)+'.'+a.name as TableNameFull from sys.objects a 
	                                    where a.name like '%_dq' and a.type='u' and SCHEMA_NAME(a.schema_id)='FACT'
                                    ) c 
                                    inner join Working_db.DQ_Config b on c.TableName = (b.TableName+'_DQ') ";
            string sqlWhere = string.Empty;
            if (!string.IsNullOrEmpty(tableName))
            {
                sqlWhere = string.Format("where c.TableName like '%{0}%'", tableName);
            }
            sql = sql + sqlWhere;
            return conn.GetJson(sql);
        }
    }
}
